﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;


namespace Atentis_Connector
{
	class SQLServerTransport
	{
		private const string TIMETOSTRINGTEMPLATE = "MM/dd/yyyy hh:mm:ss.fff";
		private const string DEFDBNAME = "test";

		private bool m_DBHasTableOrderbook = false;
		private bool m_DBHasTableSecurities = false;
		private bool m_DBHasTableIndexes = false;
		private bool m_DBHasTableNews = false;
		private bool m_DBHasTableOrders = false;
		private bool m_DBHasTablePositions = false;
		private bool m_DBHasTableStopOrders = false;
		private bool m_DBHasTableTrades = false;
		private bool m_DBHasTableTrdAcc = false;
		private bool m_DBHasTableFirm_Holding_Total = false;
		private bool m_DBHasTableHistory = false;
		private bool m_DBHasTableHistory_Daily = false;
		private bool m_DBHasTableSystime = false;
		private bool m_DBHasTableAll_Trades = false;


		/*Объявляем строковую переменную и записываем в нее
					строку подключения 
					Data Source - имя сервера, по стандарту (local)\SQLEXPRESS
					Initial Catalog - имя БД 
					Integrated Security=-параметры безопасности
					Мое подключение имеет вид
					*/
		string m_ConnectionString = string.Format(@"Data Source=(local)\SQLEXPRESS;
							Initial Catalog={0};
							Integrated Security=True;
							MultipleActiveResultSets=True", DEFDBNAME);

		public SQLServerTransport()
		{
			//Создаем БД если ее нет
			ConnectToDB();
		}

		#region Подключение/Отключение

		private void ConnectToDB()
		{

			/*Здесь указал имя БД(хотя для создания БД его указывать не нужно)
			  для того, чтобы проверить, может данная БД уже создана
			Создаем экземпляр класса  SqlConnection по имени conn
			и передаем конструктору этого класса, строку подключения
			 */
			using (var conn = new SqlConnection(m_ConnectionString))
			{
				try
				{
					//пробуем подключится
					conn.Open();
				}
				catch (SqlException se)
				{
					// Если база не обнаружена, то создаем новую
					if (se.Number == 4060)
					{
						Console.WriteLine("Подождите, идет создание БД");
						//закрываем соединение
						conn.Close();
						//переопределяем обьект conn, и передаем новую строку подключения
						using (var conn2 = new SqlConnection(@"Data Source=(local)\SQLEXPRESS;Integrated Security=True"))
						{
							/*Создаем экземпляр класса  SqlCommand по имени cmdCreateDataBase
							 и передаем конструктору этого класса, запрос на создание БД
							 и объект типа SqlConnection
							  */
							SqlCommand cmdCreateDataBase = new SqlCommand(string.Format("CREATE DATABASE [{0}]", DEFDBNAME), conn2);
							//открываем подключение
							conn2.Open();
							/*Посылаем запрос к СУБД
							 В данном случае, в результате запроса ничего не возврашается
							 ExecuteNonQuery, в последующих примерах мы будем использовать
							 и другие методы
							 */
							Console.WriteLine("Посылаем запрос");
							cmdCreateDataBase.ExecuteNonQuery();
							//закрываем подключение
							conn2.Close();
							//задержка, нужна для того, чтоб БД успела создаться
						}
						Thread.Sleep(5000);
					}
				}
				finally
				{
					Console.WriteLine("Соедение успешно произведено");
				}
			}
 
		}

		#endregion Подключение/Отключение

		#region Вполнение запросов

		private void DoQuery(string query)
		{
			using (var mssqlconnection = new SqlConnection(m_ConnectionString))
			{
				//Подключение открывается
				mssqlconnection.Open();
				//Создается команда
				SqlCommand mssqlcommand = mssqlconnection.CreateCommand();
				// SQL запрос команды
				mssqlcommand.CommandText = query;
				//Запуск класса который считывает данные из БД
				mssqlcommand.ExecuteNonQuery();
			}

		}

		#endregion Выполнение запросов

		#region Запись в БД

		public void SaveData(string tableName, List<WrapperSecurities> m_Securities)
		{
			//----------------------------------------------------------------------
			if (m_Securities != null && m_Securities.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Securities)
				{

					if (data != null)
					{
						//SHORTNAME, SECBOARD, SECCODE, SECNAME, DECIMALS, BID, OFFER, LOW, HIGH, PREVPRICE, OPEN, LAST, NUMTRADES, TIME, STEPPRICE, TRADINGSTATUS, TICKER
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableSecurities)
						{
							DoQuery("CREATE TABLE " +
							DEFDBNAME+".[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[ShortName] [char](10), "+
							"[SecBoard] [char](25), " +
							"[SecCode] [char](25), " +
							"[SecName] [char](100), " +
							"[Decimals] [char](10), "+
							"[Bid] [char](10), "+
							"[Offer] [char](10), " +
							"[Low] [char](10), " +
							"[High] [char](10), " +
							"[PrevPrice] [char](10), " +
							"[Open] [char](10), " +
							"[Last] [char](10), " +
							"[NumTrades] [char](10), " +
							"[StepPrice] [char](10), " +
							"[TradingStatus] [char](1), " +
							"[Ticker] [char](20))");
							m_DBHasTableSecurities = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"(" +
									"[MachineTime], " +
									"[ShortName], "+
									"[SecBoard], " +
									"[SecCode], " +
									"[SecName], " +
									"[Decimals], " +
									"[Bid], " +
									"[Offer], " +
									"[Low], " +
									"[High], " +
									"[PrevPrice], " +
									"[Open], " +
									"[Last], " +
									"[NumTrades], " +
									"[StepPrice], " +
									"[TradingStatus], " +
									"[Ticker]" +
							")VALUES(" +
								"'" + m_Time + "'" +
								", '"  + data.ShortName + "'" +
								", '"  + data.SecBoard + "'" +
								", '"  + data.SecCode + "'" +
								", '"  + data.SecName + "'" +
								", '"  + data.Decimals + "'" +
								", '"  + data.Bid + "'" +
								", '"  + data.Offer + "'" +
								", '"  + data.Low + "'" +
								", '"  + data.High+ "'" +
								", '"  + data.PrevPrice + "'" +
								", '"  + data.Open + "'" +
								", '"  + data.Last + "'" +
								", '"  + data.NumTrades + "'" +
								", '"  + data.StepPrice + "'" +
								", '"  + data.TradingStatus + "'" +
								", '"  + data.Ticker + "'" +
								")");
					}
				}
			}
			//----------------------------------------------------------------------
		}
		public void SaveData(string tableName, List<WrapperIndexes> m_Indexes)
		{
			//----------------------------------------------------------------------
			if (m_Indexes != null && m_Indexes.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Indexes)
				{

					if (data != null)
					{
						//"SHORTNAME, NAME, DECIMALS, CURRENTVALUE, HIGH, LOW, INDEXBOARD, INDEXCODE, PREVCLOSE, TIME, USDRATE"
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableIndexes)
						{
							DoQuery("CREATE TABLE " +
							DEFDBNAME+".[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[ShortName] [char](10), " +
							"[Name] [char](100), " +
							"[Decimals] [char](10), " +
							"[CurrentValue] [char](10), " +
							"[High] [char](10), " +
							"[Low] [char](10), " +
							"[IndexBoard] [char](25), " +
							"[IndexCode] [char](25), " +
							"[PrevClose] [char](10), " +
							"[UsdRate] [char](10))");
							m_DBHasTableIndexes = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"(" +
								"[MachineTime]," +
								"[ShortName]," +
								"[Name]," +
								"[Decimals]," +
								"[CurrentValue]," +
								"[High]," +
								"[Low]," +
								"[IndexBoard]," +
								"[IndexCode]," +
								"[PrevClose]," +
								"[UsdRate]" +
							")VALUES(" +
								"'" + m_Time + "'" +
								", '" + data.ShortName + "'" +
								", '" + data.Name + "'" +
								", '" + data.Decimals + "'" +
								", '" + data.CurrentValue + "'" +
								", '" + data.High + "'" +
								", '" + data.Low + "'" +
								", '" + data.IndexBoard + "'" +
								", '" + data.IndexCode + "'" +
								", '" + data.PrevClose + "'" +
								", '" + data.UsdRate + "'" +
								")");
					}
				}
			}
			//----------------------------------------------------------------------
		}
		public void SaveData(string tableName, List<WrapperNews> m_News)
		{
			if (m_News != null && m_News.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_News)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableNews)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[NewsNo] [char](10), " +
							"[Source] [char](250), " +
							"[Subject] [char](250), " +
							"[TerminalTime] [char](25), " +
							"[ContentType] [nvarchar](max), " +
							"[Body] [nvarchar](max))");
							m_DBHasTableNews = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								",[NewsNo]" +
								",[Source]" +
								",[Subject]" +
								",[TerminalTime]" +
								",[ContentType]" +
								",[Body])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.NewsNo + "'" +
								", '" + data.Source + "'" +
								", '" + data.Subject + "'" +
								", '" + data.Time + "'" +
								", '" + data.ContentType.Replace("'","*") + "'" +
								", '" + data.Body + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperOrders> m_Orders)
		{
			if (m_Orders != null && m_Orders.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Orders)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableOrders)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[Account] [char](25), " +
							"[AccruedInt] [char](10), " +
							"[Balance] [char](10), " +
							"[BrokerID] [char](25), " +
							"[BrokerRef] [char](25), " +
							"[BuySell] [char](1), " +
							"[EnterType] [char](1), " +
							"[EntryType] [char](1), " +
							"[ExtRef] [char](25), " +
							"[FirmID] [char](25), " +
							"[ImmCancel] [char](1), " +
							"[MarketMaker] [char](1), " +
							"[MktLimit] [char](1), " +
							"[OrderNo] [char](25), " +
							"[OrderTime] [char](25), " +
							"[Period] [char](10), " +
							"[Price] [char](10), " +
							"[Price2] [char](10), " +
							"[Quantity] [char](10), " +
							"[SecBoard] [char](25), " +
							"[SecCode] [char](50), " +
							"[SecID] [char](10), " +
							"[SplitFlag] [char](1), " +
							"[Status] [char](1), " +
							"[UserID] [char](25), " +
							"[Value] [char](10), " +
							"[WithdrawTime] [char](25), " +
							"[Yield] [char](10))");
							m_DBHasTableOrders = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								",[Account]" +
								",[AccruedInt]" +
								",[Balance]" +
								",[BrokerID]" +
								",[BrokerRef]" +
								",[BuySell]" +
								",[EnterType]" +
								",[EntryType]" +
								",[ExtRef]" +
								",[FirmID]" +
								",[ImmCancel]" +
								",[MarketMaker]" +
								",[MktLimit]" +
								",[OrderNo]" +
								",[OrderTime]" +
								",[Period]" +
								",[Price]" +
								",[Price2]" +
								",[Quantity]" +
								",[SecBoard]" +
								",[SecCode]" +
								",[SecID]" +
								",[SplitFlag]" +
								",[Status]" +
								",[UserID]" +
								",[Value]" +
								",[WithdrawTime]" +
								",[Yield])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.Account + "'" +
								", '" + data.AccruedInt + "'" +
								", '" + data.Balance + "'" +
								", '" + data.BrokerID + "'" +
								", '" + data.BrokerRef + "'" +
								", '" + data.BuySell + "'" +
								", '" + data.EnterType + "'" +
								", '" + data.EntryType + "'" +
								", '" + data.ExtRef + "'" +
								", '" + data.FirmID + "'" +
								", '" + data.ImmCancel + "'" +
								", '" + data.MarketMaker + "'" +
								", '" + data.MktLimit + "'" +
								", '" + data.OrderNo + "'" +
								", '" + data.OrderTime + "'" +
								", '" + data.Period + "'" +
								", '" + data.Price + "'" +
								", '" + data.Price2 + "'" +
								", '" + data.Quantity + "'" +
								", '" + data.SecBoard + "'" +
								", '" + data.SecCode + "'" +
								", '" + data.SecID + "'" +
								", '" + data.SplitFlag + "'" +
								", '" + data.Status + "'" +
								", '" + data.UserID + "'" +
								", '" + data.Value + "'" +
								", '" + data.WithdrawTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.Yield + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperOrderbook> m_Orderbook)
		{
			if (m_Orderbook != null && m_Orderbook.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Orderbook)
				{
					
					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableOrderbook)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[SecCode] [char](25), " +
							"[SecBoard] [char](25), " +
							"[BuySell] [char](1), " +
							"[Price] [char](10), " +
							"[Quantity] [char](10))");
							m_DBHasTableOrderbook = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								",[SecCode]" +
								",[SecBoard]" +
								",[BuySell]" +
								",[Price]" +
								",[Quantity])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.SecBoard + "'" +
								", '" + data.SecCode + "'" +
								", '" + data.BuySell + "'" +
								", '" + data.Price + "'" +
								", '" + data.Quantity + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperPositions> m_Positions)
		{
			if (m_Positions != null && m_Positions.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Positions)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTablePositions)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[AccCode] [char](1), " +
							"[BankAccID] [char](25), " +
							"[CBPLimit] [char](10), " +
							"[CBPLUsed] [char](10), " +
							"[CBPPlanned] [char](10), " +
							"[ClientID] [char](10), " +
							"[Commission] [char](10), " +
							"[Commission2] [char](10), " +
							"[CurrCode] [char](25), " +
							"[CurrentPos] [char](10), " +
							"[Description] [char](50), " +
							"[FirmID] [char](25), " +
							"[Free] [char](25), " +
							"[FreeForMargin] [char](10), " +
							"[FreeForNonMargin] [char](10), " +
							"[FreeForShorting] [char](10), " +
							"[GuaranteeFactor] [char](10), " +
							"[LiquidityRate] [char](10), " +
							"[MarginLevel] [char](10), " +
							"[MarginLevelPlanned] [char](10), " +
							"[MarginTrading] [char](10), " +
							"[Netto] [char](10), " +
							"[OpenBal] [char](10), " +
							"[OrderBuy] [char](10), " +
							"[OrderSell] [char](10), " +
							"[PlannedCommission] [char](10), " +
							"[PlannedCommission2] [char](10), " +
							"[PlannedPos] [char](10), " +
							"[Portfolio] [char](10), " +
							"[PrevLimit] [char](10), " +
							"[Profit] [char](10), " +
							"[Reserved] [char](10), " +
							"[Surplus] [char](10), " +
							"[Tag] [char](25), " +
							"[TrdAccID] [char](25), " +
							"[UseMarginLevel] [char](10), " +
							"[Value] [char](10), " +
							"[VarMargin] [char](10), " +
							"[VarMarginOpt] [char](10), " +
							"[VarMarginReal] [char](10))");
							m_DBHasTablePositions = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [AccCode]" +
								", [BankAccID]" +
								", [CBPLimit]" +
								", [CBPLUsed]" +
								", [CBPPlanned]" +
								", [ClientID]" +
								", [Commission]" +
								", [Commission2]" +
								", [CurrCode]" +
								", [CurrentPos]" +
								", [Description]" +
								", [FirmID]" +
								", [Free]" +
								", [FreeForMargin]" +
								", [FreeForNonMargin]" +
								", [FreeForShorting]" +
								", [GuaranteeFactor]" +
								", [LiquidityRate]" +
								", [MarginLevel]" +
								", [MarginLevelPlanned]" +
								", [MarginTrading]" +
								", [Netto]" +
								", [OpenBal]" +
								", [OrderBuy]" +
								", [OrderSell]" +
								", [PlannedCommission]" +
								", [PlannedCommission2]" +
								", [PlannedPos]" +
								", [Portfolio]" +
								", [PrevLimit]" +
								", [Profit]" +
								", [Reserved]" +
								", [Surplus]" +
								", [Tag]" +
								", [TrdAccID]" +
								", [UseMarginLevel]" +
								", [Value]" +
								", [VarMargin]" +
								", [VarMarginOpt]" +
								", [VarMarginReal])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.AccCode + "'" +
								", '" + data.BankAccID + "'" +
								", '" + data.CBPLimit + "'" +
								", '" + data.CBPLUsed + "'" +
								", '" + data.CBPPlanned + "'" +
								", '" + data.ClientID + "'" +
								", '" + data.Commission + "'" +
								", '" + data.Commission2 + "'" +
								", '" + data.CurrCode + "'" +
								", '" + data.CurrentPos + "'" +
								", '" + data.Description + "'" +
								", '" + data.FirmID + "'" +
								", '" + data.Free + "'" +
								", '" + data.FreeForMargin + "'" +
								", '" + data.FreeForNonMargin + "'" +
								", '" + data.FreeForShorting + "'" +
								", '" + data.GuaranteeFactor + "'" +
								", '" + data.LiquidityRate + "'" +
								", '" + data.MarginLevel + "'" +
								", '" + data.MarginLevelPlanned + "'" +
								", '" + data.MarginTrading + "'" +
								", '" + data.Netto + "'" +
								", '" + data.OpenBal + "'" +
								", '" + data.OrderBuy + "'" +
								", '" + data.OrderSell + "'" +
								", '" + data.PlannedCommission + "'" +
								", '" + data.PlannedCommission2 + "'" +
								", '" + data.PlannedPos + "'" +
								", '" + data.Portfolio + "'" +
								", '" + data.PrevLimit + "'" +
								", '" + data.Profit + "'" +
								", '" + data.Reserved + "'" +
								", '" + data.Surplus + "'" +
								", '" + data.Tag + "'" +
								", '" + data.TrdAccID + "'" +
								", '" + data.UseMarginLevel + "'" +
								", '" + data.Value + "'" +
								", '" + data.VarMargin + "'" +
								", '" + data.VarMarginOpt + "'" +
								", '" + data.VarMarginReal + "'" +
								")");


					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperStopOrders> m_StopOrders)
		{
			if (m_StopOrders != null && m_StopOrders.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_StopOrders)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableStopOrders)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[Account] [char](25), " +
							"[AccruedInt] [char](10), " +
							"[BrokerID] [char](25), " +
							"[BrokerRef] [char](25), " +
							"[BuySell] [char](1), " +
							"[EndTime] [char](25), " +
							"[ExtRef] [char](25), " +
							"[ImmCancel] [char](1), " +
							"[MarketMaker] [char](1), " +
							"[MktLimit] [char](1), " +
							"[OrderNo] [char](10), " +
							"[OrderTime] [char](25), " +
							"[OrderEndTime] [char](25), " +
							"[Price] [char](10), " +
							"[Quantity] [char](10), " +
							"[SecBoard] [char](25), " +
							"[SecCode] [char](25), " +
							"[SecID] [char](10), " +
							"[SplitFlag] [char](1), " +
							"[Status] [char](1), " +
							"[StopType] [char](1), " +
							"[WithdrawTime] [char](25), " +
							"[Yield] [char](10))");
							m_DBHasTableStopOrders = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								",[Account]" +
								",[AccruedInt]" +
								",[AlertPrice]" +
								",[BrokerID]" +
								",[BrokerRef]" +
								",[BuySell]" +
								",[EndTime]" +
								",[ExtRef]" +
								",[ImmCancel]" +
								",[MarketMaker]" +
								",[MktLimit]" +
								",[OrderNo]" +
								",[OrderTime]" +
								",[OrderEndTime]" +
								",[Price]" +
								",[Quantity]" +
								",[SecBoard]" +
								",[SecCode]" +
								",[SplitFlag]" +
								",[Status]" +
								",[StopType]" +
								",[WithdrawTime]" +
								",[Yield])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.Account + "'" +
								", '" + data.AccruedInt + "'" +
								", '" + data.AlertPrice + "'" +
								", '" + data.BrokerID + "'" +
								", '" + data.BrokerRef + "'" +
								", '" + data.BuySell + "'" +
								", '" + data.EndTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.ExtRef + "'" +
								", '" + data.ImmCancel + "'" +
								", '" + data.MktLimit + "'" +
								", '" + data.OrderNo + "'" +
								", '" + data.OrderTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.OrderEndTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.Price + "'" +
								", '" + data.Quantity + "'" +
								", '" + data.SecBoard + "'" +
								", '" + data.SecCode + "'" +
								", '" + data.SplitFlag + "'" +
								", '" + data.Status + "'" +
								", '" + data.StopType + "'" +
								", '" + data.WithdrawTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.Yield + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperTrades> m_Trades)
		{
			if (m_Trades != null && m_Trades.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Trades)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableTrades)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[Account] [char](25), " +
							"[AccruedInt] [char](10), " +
							"[BrokerID] [char](25), " +
							"[BrokerRef] [char](25), " +
							"[BuySell] [char](1), " +
							"[Commission] [char](10), " +
							"[ExtRef] [char](25), " +
							"[FirmID] [char](25), " +
							"[IssueCode] [char](25), " +
							"[OrderNo] [char](25), " +
							"[Period] [char](1), " +
							"[Price] [char](10), " +
							"[Quantity] [char](10), " +
							"[RepoRate] [char](10), " +
							"[RepoTerm] [char](10), " +
							"[SecBoard] [char](25), " +
							"[SecCode] [char](25), " +
							"[SecID] [char](10), " +
							"[SettleCode] [char](25), " +
							"[StartDiscount] [char](10), " +
							"[TradeNo] [char](25), " +
							"[TradeTime] [char](25), " +
							"[TradeType] [char](1), " +
							"[UserID] [char](25), " +
							"[Value] [char](10), " +
							"[Yield] [char](10))");
							m_DBHasTableTrades = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [Account]" +
								", [AccruedInt]" +
								", [BrokerID]" +
								", [BrokerRef]" +
								", [BuySell]" +
								", [Commission]" +
								", [ExtRef]" +
								", [FirmID]" +
								", [IssueCode]" +
								", [OrderNo]" +
								", [Period]" +
								", [Price]" +
								", [Quantity]" +
								", [RepoRate]" +
								", [RepoTerm]" +
								", [SecBoard]" +
								", [SecCode]" +
								", [SecID]" +
								", [SettleCode]" +
								", [StartDiscount]" +
								", [TradeNo]" +
								", [TradeTime]" +
								", [TradeType]" +
								", [UserID]" +
								", [Value]" +
								", [Yield])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.Account + "'" +
								", '" + data.AccruedInt + "'" +
								", '" + data.BrokerID + "'" +
								", '" + data.BrokerRef + "'" +
								", '" + data.BuySell + "'" +
								", '" + data.Commission + "'" +
								", '" + data.ExtRef + "'" +
								", '" + data.FirmID + "'" +
								", '" + data.IssueCode + "'" +
								", '" + data.OrderNo + "'" +
								", '" + data.Period + "'" +
								", '" + data.Price + "'" +
								", '" + data.Quantity + "'" +
								", '" + data.RepoRate + "'" +
								", '" + data.RepoTerm + "'" +
								", '" + data.SecBoard + "'" +
								", '" + data.SecCode + "'" +
								", '" + data.SecID + "'" +
								", '" + data.SettleCode + "'" +
								", '" + data.StartDiscount + "'" +
								", '" + data.TradeNo + "'" +
								", '" + data.TradeTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.TradeType + "'" +
								", '" + data.UserID + "'" +
								", '" + data.Value + "'" +
								", '" + data.Yield + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperTrdAcc> m_TrdAcc)
		{
			if (m_TrdAcc != null && m_TrdAcc.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_TrdAcc)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableTrdAcc)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[AccType] [char](25), " +
							"[BankAccount] [char](25), " +
							"[BankCode] [char](25), " +
							"[DepoAccount] [char](25), " +
							"[DepoCode] [char](25), " +
							"[Description] [char](50), " +
							"[FirmID] [char](25), " +
							"[MainTrdAccID] [char](25), " +
							"[Name] [char](25), " +
							"[TrdAccID] [char](25))");
							m_DBHasTableTrdAcc = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [AccType]" +
								", [BankAccount]" +
								", [BankCode]" +
								", [DepoAccount]" +
								", [DepoCode]" +
								", [Description]" +
								", [FirmID]" +
								", [MainTrdAccID]" +
								", [Name]" +
								", [TrdAccID])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.AccType + "'" +
								", '" + data.BankAccount + "'" +
								", '" + data.BankCode + "'" +
								", '" + data.DepoAccount + "'" +
								", '" + data.DepoCode + "'" +
								", '" + data.Description + "'" +
								", '" + data.FirmID + "'" +
								", '" + data.MainTrdAccID + "'" +
								", '" + data.Name + "'" +
								", '" + data.TrdAccID + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperFirm_Holding_Total> m_Firm_Holding_Total)
		{
			if (m_Firm_Holding_Total != null && m_Firm_Holding_Total.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Firm_Holding_Total)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableFirm_Holding_Total)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[AccType] [char](25), " +
							"[ClientID] [char](10), " +
							"[CurrentPos] [char](25), " +
							"[FirmID] [char](25), " +
							"[IssueCode] [char](25), " +
							"[Min] [char](10), " +
							"[OpenBal] [char](10), " +
							"[PlannedPosBuy] [char](10), " +
							"[PlannedPosSell] [char](10), " +
							"[SecCode] [char](25), " +
							"[Ticker] [char](25), " +
							"[TodayBuy] [char](10), " +
							"[TodaySell] [char](10), " +
							"[TrdAccID] [char](25), " +
							"[VarMargin] [char](10), " +
							"[VarMarginOpt] [char](10), " +
							"[VarMarginReal] [char](10))");
							m_DBHasTableFirm_Holding_Total = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [AccType]" +
								", [ClientID]" +
								", [CurrentPos]" +
								", [FirmID]" +
								", [IssueCode]" +
								", [Min]" +
								", [OpenBal]" +
								", [PlannedPosBuy]" +
								", [PlannedPosSell]" +
								", [SecCode]" +
								", [Ticker]" +
								", [TodayBuy]" +
								", [TodaySell]" +
								", [TrdAccID]" +
								", [VarMargin]" +
								", [VarMarginOpt]" +
								", [VarMarginReal])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.AccType + "'" +
								", '" + data.ClientID + "'" +
								", '" + data.CurrentPos + "'" +
								", '" + data.FirmID + "'" +
								", '" + data.IssueCode + "'" +
								", '" + data.Min + "'" +
								", '" + data.OpenBal + "'" +
								", '" + data.PlannedPosBuy + "'" +
								", '" + data.PlannedPosSell + "'" +
								", '" + data.SecCode + "'" +
								", '" + data.Ticker + "'" +
								", '" + data.TodayBuy + "'" +
								", '" + data.TodaySell + "'" +
								", '" + data.TrdAccID + "'" +
								", '" + data.VarMargin + "'" +
								", '" + data.VarMarginOpt + "'" +
								", '" + data.VarMarginReal + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperHistory> m_History)
		{
			if (m_History != null && m_History.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_History)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableHistory)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[Close] [char](10), " +
							"[High] [char](10), " +
							"[Low] [char](10), " +
							"[Open] [char](10), " +
							"[Period] [char](10), " +
							"[RecID] [char](10), " +
							"[Ticker] [char](25), " +
							"[Time] [char](25), " +
							"[Volume] [char](10))");
							m_DBHasTableHistory = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [Close]" +
								", [High]" +
								", [Low]" +
								", [Open]" +
								", [Period]" +
								", [RecID]" +
								", [Ticker]" +
								", [Time]" +
								", [Volume])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.Close + "'" +
								", '" + data.High + "'" +
								", '" + data.Low + "'" +
								", '" + data.Open + "'" +
								", '" + data.Period + "'" +
								", '" + data.RecID + "'" +
								", '" + data.Ticker + "'" +
								", '" + data.Time.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.Volume + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperHistory_Daily> m_History_Daily)
		{
			if (m_History_Daily != null && m_History_Daily.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_History_Daily)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableHistory_Daily)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[Close] [char](10), " +
							"[High] [char](10), " +
							"[Low] [char](10), " +
							"[Open] [char](10), " +
							"[RecID] [char](10), " +
							"[Ticker] [char](25), " +
							"[Time] [char](25), " +
							"[Volume] [char](10))");
							m_DBHasTableHistory_Daily = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [Close]" +
								", [High]" +
								", [Low]" +
								", [Open]" +
								", [RecID]" +
								", [Ticker]" +
								", [Time]" +
								", [Volume])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.Close + "'" +
								", '" + data.High + "'" +
								", '" + data.Low + "'" +
								", '" + data.Open + "'" +
								", '" + data.RecID + "'" +
								", '" + data.Ticker + "'" +
								", '" + data.Time.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								", '" + data.Volume + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperBoards> m_Boards)
		{
			if (m_Boards != null && m_Boards.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Boards)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableHistory_Daily)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[BoardID] [char](25), " +
							"[BoardName] [char](250), " +
							"[MarketID] [char](25), " +
							"[Status] [char](25))");
							m_DBHasTableHistory_Daily = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [BoardID]" +
								", [BoardName]" +
								", [MarketID]" +
								", [Status])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.BoardID + "'" +
								", '" + data.BoardName + "'" +
								", '" + data.MarketID + "'" +
								", '" + data.Status + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperTesystime> m_Systime)
		{
			if (m_Systime != null && m_Systime.Count() > 0)
			{
				string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
				foreach (var data in m_Systime)
				{

					if (data != null)
					{
						// SecBoard, SecCode, BuySell, Price, Quantity
						//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
						if (!IsDBHasTable(tableName) && !m_DBHasTableSystime)
						{
							DoQuery("CREATE TABLE " +
							"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
							"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
							"[Time] [char](25))");
							m_DBHasTableSystime = true;
						}
						DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
								"([MachineTime]" +
								", [Time])" +
							"VALUES" +
								"(" +
								"'" + m_Time + "'" +
								", '" + data.Time.ToString(TIMETOSTRINGTEMPLATE) + "'" +
								")");
					}
				}
			}
		}
		public void SaveData(string tableName, List<WrapperAll_Trades> m_All_Trades)
		{
			{
				if (m_All_Trades != null && m_All_Trades.Count() > 0)
				{
					string m_Time = DateTime.Now.ToString(TIMETOSTRINGTEMPLATE);
					foreach (var data in m_All_Trades)
					{

						if (data != null)
						{
							// SecBoard, SecCode, BuySell, Price, Quantity
							//Дата время будут сохраняться как строка шаблона: "YYYYMMDD HH:MM:SS"
							if (!IsDBHasTable(tableName) && !m_DBHasTableAll_Trades)
							{
								DoQuery("CREATE TABLE " +
								"["+DEFDBNAME+"].[dbo].[" + tableName + "]" + "(" +
								"[MachineTime] [char](" + (TIMETOSTRINGTEMPLATE.Count()) + "), " +
								"[AccruedInt] [char](10), " +
								"[Period] [char](1), " +
								"[Price] [char](10), " +
								"[Quantity] [char](10), " +
								"[RepoRate] [char](10), " +
								"[RepoTerm] [char](10), " +
								"[RowState] [char](10), " +
								"[SecBoard] [char](25), " +
								"[SecCode] [char](25), " +
								"[SecID] [char](10), " +
								"[SettleCode] [char](25), " +
								"[StartDiscount] [char](10), " +
								"[TradeNo] [char](10), " +
								"[TradeTime] [char](25), " +
								"[TradeType] [char](1), " +
								"[Value] [char](10), " +
								"[Yield] [char](10))");
								m_DBHasTableAll_Trades = true;
							}
							DoQuery("INSERT INTO "+DEFDBNAME+".[dbo].[" + tableName + "]" +
									"([MachineTime]" +
									", [AccruedInt]" +
									", [Period]" +
									", [Price]" +
									", [Quantity]" +
									", [RepoRate]" +
									", [RepoTerm]" +
									", [RowState]" +
									", [SecBoard]" +
									", [SecCode]" +
									", [SecID]" +
									", [SettleCode]" +
									", [StartDiscount]" +
									", [TradeNo]" +
									", [TradeTime]" +
									", [TradeType]" +
									", [Value]" +
									", [Yield])" +
								"VALUES" +
									"(" +
									"'" + m_Time + "'" +
									", '" + data.AccruedInt + "'" +
									", '" + data.Period + "'" +
									", '" + data.Price + "'" +
									", '" + data.Quantity + "'" +
									", '" + data.RepoRate + "'" +
									", '" + data.RepoTerm + "'" +
									", '" + data.RowState + "'" +
									", '" + data.SecBoard + "'" +
									", '" + data.SecCode + "'" +
									", '" + data.SecID + "'" +
									", '" + data.SettleCode + "'" +
									", '" + data.StartDiscount + "'" +
									", '" + data.TradeNo + "'" +
									", '" + data.TradeTime.ToString(TIMETOSTRINGTEMPLATE) + "'" +
									", '" + data.TradeType + "'" +
									", '" + data.Value + "'" +
									", '" + data.Yield + "'" +
									")");
						}
					}
				}
			}
		}

		#endregion Запись в БД

		#region Проверка на существование таблицы в БД
		// Проверка на существование таблицы в БД
		private bool IsDBHasTable(string tableName)
		{
			bool res = false;
			var query = string.Format("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName);
			using (var mssqlconnection = new SqlConnection(m_ConnectionString))
			{
				//Подключение открывается
				mssqlconnection.Open();
				//Создается команда
				SqlCommand mssqlcommand = mssqlconnection.CreateCommand();
				// SQL запрос команды
				mssqlcommand.CommandText = query;
				//Запуск класса который считывает данные из БД
				try
				{
					var mssqlreader = mssqlcommand.ExecuteReader();
					if (mssqlreader != null)
					{
						while (mssqlreader.Read())
							if (mssqlreader.GetString(0) == tableName)
								res = true;
					}
				}
				catch (Exception e)
				{
					Console.WriteLine("{0} Exception caught in 'IsDBHasTable()' method.", e);
				}
			}

			return res;
		}
		#endregion Проверка на существование таблицы в БД

	}
}
